using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml.Serialization;

namespace UDS.Management.Data
{
    static class DBDataJobs
    {
        public static void LoadDBJobsData(SQLJobDefinition sqlJobDefinition, SqlConnection Connection)
        {
            if (sqlJobDefinition != null)
            {
                using (SqlTransaction trans = Connection.BeginTransaction())
                {
                    try
                    {
                        SqlParameter[] jobParameters = new SqlParameter[8];
                        jobParameters[0] = new SqlParameter("@JobName", SqlDbType.VarChar, 255);
                        jobParameters[0].Value = sqlJobDefinition.JobName;
                        jobParameters[1] = new SqlParameter("@JobVersion", SqlDbType.VarChar, 255);
                        jobParameters[1].Value = sqlJobDefinition.JobVersion;
                        jobParameters[2] = new SqlParameter("@OldJobName", SqlDbType.VarChar, 255);
                        jobParameters[2].Value = sqlJobDefinition.OldJobName;
                        jobParameters[3] = new SqlParameter("@Category", SqlDbType.VarChar, 255);
                        jobParameters[3].Value = sqlJobDefinition.Category;
                        jobParameters[4] = new SqlParameter("@Description", SqlDbType.VarChar, 500);
                        jobParameters[4].Value = ((sqlJobDefinition.Description == null) ? string.Empty : sqlJobDefinition.Description);
                        jobParameters[5] = new SqlParameter("@Enabled", SqlDbType.Bit);
                        jobParameters[5].Value = sqlJobDefinition.Enabled;
                        jobParameters[6] = new SqlParameter("@Owner", SqlDbType.VarChar, 100);
                        jobParameters[6].Value = sqlJobDefinition.Owner;
                        jobParameters[7] = new SqlParameter("@StartStepID", SqlDbType.Int);
                        jobParameters[7].Value = sqlJobDefinition.StartStepID;

                        Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertDBSQLJob", 20, jobParameters);

                        SqlParameter[] stepDelParameters = new SqlParameter[2];
                        stepDelParameters[0] = new SqlParameter("@JobName", SqlDbType.VarChar, 255);
                        stepDelParameters[0].Value = sqlJobDefinition.JobName;
                        stepDelParameters[1] = new SqlParameter("@JobVersion", SqlDbType.VarChar, 255);
                        stepDelParameters[1].Value = sqlJobDefinition.JobVersion;

                        Execute.ExecuteStoredProcedure(trans, "sproc_repl_DeleteDBJobSteps", 20, stepDelParameters);

                        //fill the Job Steps
                        if (sqlJobDefinition.JobSteps != null)
                        {
                            foreach (JobStepDefinition jobStep in sqlJobDefinition.JobSteps)
                            {
                                SqlParameter[] stepParameters = new SqlParameter[13];
                                stepParameters[0] = new SqlParameter("@JobName", SqlDbType.VarChar, 255);
                                stepParameters[0].Value = sqlJobDefinition.JobName;
                                stepParameters[1] = new SqlParameter("@JobVersion", SqlDbType.VarChar, 255);
                                stepParameters[1].Value = sqlJobDefinition.JobVersion;
                                stepParameters[2] = new SqlParameter("@StepId", SqlDbType.Int);
                                stepParameters[2].Value = jobStep.StepId;
                                stepParameters[3] = new SqlParameter("@StepName", SqlDbType.VarChar, 255);
                                stepParameters[3].Value = jobStep.StepName;
                                stepParameters[4] = new SqlParameter("@AgentSubSystem", SqlDbType.VarChar, 255);
                                stepParameters[4].Value = jobStep.AgentSubSystem;
                                stepParameters[5] = new SqlParameter("@DatabaseName", SqlDbType.VarChar, 255);
                                stepParameters[5].Value = jobStep.DatabaseName;
                                stepParameters[6] = new SqlParameter("@StepCommand", SqlDbType.Text);
                                stepParameters[6].Value = jobStep.StepCommand;
                                stepParameters[7] = new SqlParameter("@RetryAttempts", SqlDbType.Int);
                                stepParameters[7].Value = jobStep.RetryAttempts;
                                stepParameters[8] = new SqlParameter("@RetryAttemptsInterval", SqlDbType.Int);
                                stepParameters[8].Value = jobStep.RetryAttemptsInterval;
                                stepParameters[9] = new SqlParameter("@OnSuccessAction", SqlDbType.VarChar, 255);
                                stepParameters[9].Value = jobStep.OnSuccessAction;
                                stepParameters[10] = new SqlParameter("@OnSuccessStepId", SqlDbType.Int);
                                stepParameters[10].Value = jobStep.OnSuccessStepId;
                                stepParameters[11] = new SqlParameter("@OnFailAction", SqlDbType.VarChar, 255);
                                stepParameters[11].Value = jobStep.OnFailAction;
                                stepParameters[12] = new SqlParameter("@OnFailStepId", SqlDbType.Int);
                                stepParameters[12].Value = jobStep.OnFailStepId;

                                Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertDBJobStep", 20, stepParameters);
                            }
                            SqlParameter[] jobDelParameters = new SqlParameter[2];
                            jobDelParameters[0] = new SqlParameter("@JobName", SqlDbType.VarChar, 255);
                            jobDelParameters[0].Value = sqlJobDefinition.JobName;
                            jobDelParameters[1] = new SqlParameter("@JobVersion", SqlDbType.VarChar, 255);
                            jobDelParameters[1].Value = sqlJobDefinition.JobVersion;

                            Execute.ExecuteStoredProcedure(trans, "sproc_repl_DeleteDBJobSchedules", 20, jobDelParameters);

                            if (sqlJobDefinition.JobSchedules != null)
                            {
                                foreach (JobScheduleDefinition jobSchedule in sqlJobDefinition.JobSchedules)
                                {
                                    SqlParameter[] scheduleParameters = new SqlParameter[12];
                                    scheduleParameters[0] = new SqlParameter("@JobName", SqlDbType.VarChar, 255);
                                    scheduleParameters[0].Value = sqlJobDefinition.JobName;
                                    scheduleParameters[1] = new SqlParameter("@JobVersion", SqlDbType.VarChar, 255);
                                    scheduleParameters[1].Value = sqlJobDefinition.JobVersion;
                                    scheduleParameters[2] = new SqlParameter("@ScheduleName", SqlDbType.VarChar, 255);
                                    scheduleParameters[2].Value = jobSchedule.ScheduleName;
                                    scheduleParameters[3] = new SqlParameter("@FrequencyType", SqlDbType.VarChar, 255);
                                    scheduleParameters[3].Value = jobSchedule.FrequencyType;
                                    scheduleParameters[4] = new SqlParameter("@Enabled", SqlDbType.Bit);
                                    scheduleParameters[4].Value = jobSchedule.Enabled;
                                    scheduleParameters[5] = new SqlParameter("@FrequencySubDayType", SqlDbType.VarChar, 255);
                                    scheduleParameters[5].Value = jobSchedule.FrequencySubDayType;
                                    scheduleParameters[6] = new SqlParameter("@FrequencySubDayInterval", SqlDbType.Int);
                                    scheduleParameters[6].Value = jobSchedule.FrequencySubDayInterval;
                                    scheduleParameters[7] = new SqlParameter("@FrequencyRelativeInterval", SqlDbType.Int);
                                    scheduleParameters[7].Value = jobSchedule.FrequencyRelativeInterval;
                                    scheduleParameters[8] = new SqlParameter("@FrequencyRecurrenceFactor", SqlDbType.Int);
                                    scheduleParameters[8].Value = jobSchedule.FrequencyRecurrenceFactor;
                                    scheduleParameters[9] = new SqlParameter("@FrequencyInterval", SqlDbType.Int);
                                    scheduleParameters[9].Value = jobSchedule.FrequencyInterval;
                                    scheduleParameters[10] = new SqlParameter("@ActiveStart", SqlDbType.Int);
                                    scheduleParameters[10].Value = jobSchedule.ActiveStart;
                                    scheduleParameters[11] = new SqlParameter("@ActiveEnd", SqlDbType.Int);
                                    scheduleParameters[11].Value = jobSchedule.ActiveEnd;

                                    Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertDBJobSchedule", 20, scheduleParameters);
                                }
                            }
                        }
                    }
                    catch (Exception)
                    {
                        try
                        {
                            trans.Rollback();
                        }
                        catch
                        {
                            // Ignore
                        }
                        throw;
                    }
                    trans.Commit();
                }
            }
        }

        internal static SQLJobDefinition[] GetDBXMLOfSQLJob(string databaseName, SqlConnection connection)
        {
            SqlDataReader JobsReader = null;
            List<SQLJobDefinition> list = new List<SQLJobDefinition>();
            List<string> jobNames = new List<string>();
            try
            {
                SqlParameter[] Params = new SqlParameter[1];
                Params[0] = new SqlParameter("@DatabaseName", databaseName);
                JobsReader = Execute.ExecuteReader(connection, "sproc_db_GetDBXMLOfSQLJobNames", 20, Params);
                string retVal = string.Empty;
                while (JobsReader.Read())
                {
                    jobNames.Add(JobsReader.GetString(0));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                if (JobsReader != null)
                {
                    JobsReader.Close();
                }
            }
            SqlParameter[] jobParams = new SqlParameter[1];
            foreach (string jobName in jobNames)
            {
                try
                {
                    jobParams[0] = new SqlParameter("@JobName", jobName);

                    SQLJobDefinition job = GetXMLOfSQLJob(jobName, "sproc_db_GetXMLOfSQLJob", jobParams, connection);
                    list.Add(job);
                }
                catch (Exception)
                {
                }
            }
            return list.ToArray();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="jobName">have the full _V_1_x</param>
        /// <param name="Connection"></param>
        /// <returns></returns>
        internal static SQLJobDefinition GetMSDBXMLOfSQLJob(string jobName, SqlConnection Connection)
        {
            SqlParameter[] Params = new SqlParameter[1];
            Params[0] = new SqlParameter("@JobName", jobName);
            SQLJobDefinition job = GetXMLOfSQLJob(jobName, "sproc_db_GetMSDBXMLOfSQLJob", Params, Connection);
            string jobVersion = string.Empty;
            string nonVersionedJobName = SplitJobName(jobName, out jobVersion);
            job.JobName = nonVersionedJobName;
            job.JobVersion = jobVersion;
            return job;
        }

        private static string SplitJobName(string jobName, out string jobVersion)
        {
            int pos = jobName.LastIndexOf("_V_");
            if (pos > 0)
            {
                jobVersion = jobName.Substring(pos);
                return jobName.Substring(0, pos);
            }
            else
            {
                jobVersion = string.Empty;
                return jobName;
            }
        }

        internal static void FillDBIfEmpty(string databaseName, SqlConnection connection)
        {
            SqlDataReader Reader = null;
            int retVal = 0;
            try
            {
                Reader = Execute.ExecuteReader(connection, "sproc_db_GetSQLJobCount", 20);
                while (Reader.Read())
                {
                    retVal = Reader.GetInt32(0);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (Reader != null)
                {
                    Reader.Close();
                }
            }
            try
            {
                if (retVal == 0)
                {
                    SQLJobDefinition[] jobs = GetIptvMSDBXMLOfSQLJob(databaseName, connection);
                    foreach (SQLJobDefinition job in jobs)
                    {
                        LoadDBJobsData(job, connection);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal static SQLJobDefinition[] GetIptvMSDBXMLOfSQLJob(string databaseName, SqlConnection connection)
        {
            SqlDataReader JobsReader = null;
            List<SQLJobDefinition> list = new List<SQLJobDefinition>();
            List<string> jobNames = new List<string>();
            try
            {
                SqlParameter[] Params = new SqlParameter[1];
                Params[0] = new SqlParameter("@DatabaseName", databaseName);
                JobsReader = Execute.ExecuteReader(connection, "sproc_db_GetIptvMSDBXMLOfSQLJob", 20, Params);
                string retVal = string.Empty;
                while (JobsReader.Read())
                {
                    jobNames.Add(JobsReader.GetString(0));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                if (JobsReader != null)
                {
                    JobsReader.Close();
                }
            }
            foreach (string jobName in jobNames)
            {
                try
                {
                    SQLJobDefinition job = GetMSDBXMLOfSQLJob(jobName, connection);
                    list.Add(job);
                }
                catch (Exception)
                {
                }
            }
            return list.ToArray();
        }

        private static SQLJobDefinition GetXMLOfSQLJob(string jobName, string sprocName, SqlParameter[] Params, SqlConnection Connection)
        {
            SqlDataReader Reader = null;
            try
            {

                Reader = Execute.ExecuteReader(Connection, sprocName, 20, Params);
                string retVal = string.Empty;
                while (Reader.Read())
                {
                    retVal += Reader.GetString(0);
                }
                retVal = retVal.Replace("<SQLJob ", "<SQLJob xmlns=\"http://www.sosceo.com/server/Install/SQLJobsSchema\" ");
                //                XmlDocument doc = new XmlDocument();
                //                doc.LoadXml(retVal);
                //                retVal = doc.OuterXml;
                StringReader xmlReader = new StringReader(retVal);
                XmlSerializer Serializer = new XmlSerializer(typeof(SQLJobDefinition));

                return (SQLJobDefinition)Serializer.Deserialize(xmlReader);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                if (Reader != null)
                {
                    Reader.Close();
                }
            }
        }
    }
}
